﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Runtime.InteropServices;
using System.Xml.Linq;
using Microsoft.AspNetCore.Mvc;
using work10;
namespace work10
{
    /* CREATE TABLE School(
     SchoolId INTEGER PRIMARY KEY,
     Name TEXT NOT NULL
 );

     CREATE TABLE Classroom(
         ClassroomId INTEGER PRIMARY KEY,
         Name TEXT NOT NULL,
         SchoolId INTEGER,
         FOREIGN KEY (SchoolId) REFERENCES School (SchoolId)
     );

     CREATE TABLE Student(
         StudentId INTEGER PRIMARY KEY,
         Name TEXT NOT NULL,
         ClassroomId INTEGER,
         FOREIGN KEY (ClassroomId) REFERENCES Classroom (ClassroomId)
     );

     CREATE TABLE Log(
         LogId INTEGER PRIMARY KEY,
         Action TEXT NOT NULL,
         Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
     );*/
          /*  运行和测试
        启动 Web API 项目。
        使用 Postman 发送 HTTP 请求来测试 API。
        GET /api/schools/{id
        }
        POST / api / schools
        PUT / api / schools /{ id}
        DELETE / api / schools /{ id}
        [Route("api/[controller]")]
            [ApiController]*/
    public class SchoolsController : ControllerBase
    {
        private readonly ISchoolRepository _schoolRepository;

        public SchoolsController(ISchoolRepository schoolRepository)
        {
            _schoolRepository = schoolRepository;
        }

        [HttpGet("{id}")]
        public IActionResult GetSchool(int id)
        {
            var school = _schoolRepository.GetSchool(id);
            if (school == null) return NotFound();
            return Ok(school);
        }

        [HttpPost]
        public IActionResult AddSchool([FromBody] School school)
        {
            _schoolRepository.AddSchool(school);
            return CreatedAtAction(nameof(GetSchool), new { id = school.SchoolId }, school);
        }

        [HttpPut("{id}")]
        public IActionResult UpdateSchool(int id, [FromBody] School school)
        {
            if (id != school.SchoolId) return BadRequest();
            _schoolRepository.UpdateSchool(school);
            return NoContent();
        }

        [HttpDelete("{id}")]
        public IActionResult DeleteSchool(int id)
        {
            _schoolRepository.DeleteSchool(id);
            return NoContent();
        }

        public void AddSchool(School school)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("INSERT INTO School (Name) VALUES (@Name)", connection);
                command.Parameters.AddWithValue("@Name", school.Name);
                command.ExecuteNonQuery();

                LogAction("Added School");
            }
        }

        public void AddClassroom(Classroom classroom)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("INSERT INTO Classroom (Name, SchoolId) VALUES (@Name, @SchoolId)", connection);
                command.Parameters.AddWithValue("@Name", classroom.Name);
                command.Parameters.AddWithValue("@SchoolId", classroom.SchoolId);
                command.ExecuteNonQuery();

                LogAction("Added Classroom");
            }
        }

        public void AddStudent(Student student)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("INSERT INTO Student (Name, ClassroomId) VALUES (@Name, @ClassroomId)", connection);
                command.Parameters.AddWithValue("@Name", student.Name);
                command.Parameters.AddWithValue("@ClassroomId", student.ClassroomId);
                command.ExecuteNonQuery();

                LogAction("Added Student");
            }
        }
        public void DeleteSchool(int schoolId)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("DELETE FROM School WHERE SchoolId = @SchoolId", connection);
                command.Parameters.AddWithValue("@SchoolId", schoolId);
                command.ExecuteNonQuery();

                LogAction("Deleted School");
            }
        }

        public void DeleteClassroom(int classroomId)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("DELETE FROM Classroom WHERE ClassroomId = @ClassroomId", connection);
                command.Parameters.AddWithValue("@ClassroomId", classroomId);
                command.ExecuteNonQuery();

                LogAction("Deleted Classroom");
            }
        }

        public void DeleteStudent(int studentId)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("DELETE FROM Student WHERE StudentId = @StudentId", connection);
                command.Parameters.AddWithValue("@StudentId", studentId);
                command.ExecuteNonQuery();

                LogAction("Deleted Student");
            }
        }

        public void UpdateSchool(School school)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("UPDATE School SET Name = @Name WHERE SchoolId = @SchoolId", connection);
                command.Parameters.AddWithValue("@Name", school.Name);
                command.Parameters.AddWithValue("@SchoolId", school.SchoolId);
                command.ExecuteNonQuery();

                LogAction("Updated School");
            }
        }

        public void UpdateClassroom(Classroom classroom)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("UPDATE Classroom SET Name = @Name, SchoolId = @SchoolId WHERE ClassroomId = @ClassroomId", connection);
                command.Parameters.AddWithValue("@Name", classroom.Name);
                command.Parameters.AddWithValue("@SchoolId", classroom.SchoolId);
                command.Parameters.AddWithValue("@ClassroomId", classroom.ClassroomId);
                command.ExecuteNonQuery();

                LogAction("Updated Classroom");
            }
        }

        public void UpdateStudent(Student student)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("UPDATE Student SET Name = @Name, ClassroomId = @ClassroomId WHERE StudentId = @StudentId", connection);
                command.Parameters.AddWithValue("@Name", student.Name);
                command.Parameters.AddWithValue("@ClassroomId", student.ClassroomId);
                command.Parameters.AddWithValue("@StudentId", student.StudentId);
                command.ExecuteNonQuery();

                LogAction("Updated Student");
            }
        }
        public School GetSchool(int schoolId)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("SELECT * FROM School WHERE SchoolId = @SchoolId", connection);
                command.Parameters.AddWithValue("@SchoolId", schoolId);

                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new School
                        {
                            SchoolId = reader.GetInt32(0),
                            Name = reader.GetString(1)
                        };
                    }
                }
            }
            return null;
        }

        public Classroom GetClassroom(int classroomId)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("SELECT * FROM Classroom WHERE ClassroomId = @ClassroomId", connection);
                command.Parameters.AddWithValue("@ClassroomId", classroomId);

                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new Classroom
                        {
                            ClassroomId = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            SchoolId = reader.GetInt32(2)
                        };
                    }
                }
            }
            return null;
        }

        public Student GetStudent(int studentId)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("SELECT * FROM Student WHERE StudentId = @StudentId", connection);
                command.Parameters.AddWithValue("@StudentId", studentId);

                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new Student
                        {
                            StudentId = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            ClassroomId = reader.GetInt32(2)
                        };
                    }
                }
            }
            return null;
        }
        public void LogAction(string action)
        {
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("INSERT INTO Log (Action) VALUES (@Action)", connection);
                command.Parameters.AddWithValue("@Action", action);
                command.ExecuteNonQuery();
            }
        }

        public List<string> GetLogs()
        {
            var logs = new List<string>();
            using (var connection = new SQLiteConnection("Data Source=school.db"))
            {
                connection.Open();
                var command = new SQLiteCommand("SELECT * FROM Log", connection);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        logs.Add($"[{reader.GetDateTime(2)}] {reader.GetString(1)}");
                    }
                }
            }
            return logs;
        }

    }
}